//区域覆盖分析的JAVA类
package com.ejoysoft.wlanyz.business;

import com.ejoysoft.auth.MD5;
import com.ejoysoft.common.*;
import com.ejoysoft.common.exception.UserUnitIdException;
import com.ejoysoft.wlanyz.system.Unit;
import com.ejoysoft.wlanyz.system.User;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;

import org.jfree.ui.Size2D;

import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.HashMap;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class Cityanalysis{
	private Globa globa;
    private DbConnect db;

    public Cityanalysis() {
    }

    public Cityanalysis(Globa globa) {
        this.globa = globa;
        db = globa.db;
    }

    public Cityanalysis(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        if (b) globa.setDynamicProperty(this);
    }
    String strTableName="wlan_task_info";
    String strTableName1="WLAN_TEST_RESULT";
    String strTableName2="WLAN_RESC_HOTPOT";
    String strTableName3="RM_DEVICE";
    
    ///////////////////////////////////////热点分析部分///////////////////////////////////
    //获取某一城市已测的热点的数量
    public int returntestedHpcount(String where,String city){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnhpid(where);  //获取已测的热点ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strID ='"+list.get(i)+"'"; 
        }
    	where2 +=") and strcity = '"+city+"'";
    	
    	try {
            String strSql = "select * from  " + strTableName2 + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("strid"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    	db.closeCon();		 
    	return list2.size();
    }
    //获取热点ID*****************
    public List<String> returnhpid(String where) {	  
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnTskId(where);  //获取测试结果表中的所有任务ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or STRTASKID ='"+list.get(i)+"'"; 
        }
    		 where2 +=")";
    	
        try {
            String strSql = "select distinct strhotpotname from  " + strTableName + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("STRHOTPOTNAME"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        db.closeCon();
        return list2; //返回所有相关的热点名称
    }  
    //获取已测任务ID***********************************************
    public List<String> returnTskId(String where) {
    	  List<String> list = new ArrayList<String>();
          try {
              String strSql = "select distinct strtaskid from  wlan_test_result ".concat(where);
              ResultSet rs = db.executeQuery(strSql);
              if (rs != null){
            	 while (rs.next()) {
                     list.add(rs.getString("STRTASKID"));
  				}
              }
          } catch (Exception e) {
              e.printStackTrace();
          }
          db.closeCon();
          return list;
      } 
    //获取所有不重复的地市名*****************************************
    public List<String> returnCity(String where){
    	List<String> list = new ArrayList<String>();
    	where = "";
    	try{
    		String strSql = "select distinct strcity from wlan_resc_hotpot ".concat(where);
    		ResultSet rs = db.executeQuery(strSql);
    		if(rs!=null){
    			while(rs.next()){
    				list.add(rs.getString("Strcity"));
    			}
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	db.closeCon();
    	return list;
    }
    //获取每个城市的热点总数*******************************
    public int returnHpcount(String where){
    	int count;
    	List<String> list = new ArrayList<String>();
    	try{
    		String strSql = "select * from wlan_resc_hotpot ".concat(where);
    		ResultSet rs = db.executeQuery(strSql);
    		if(rs!=null){
    			while(rs.next()){
    				list.add(rs.getString("Strid"));
    			}
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	count = list.size();
    	db.closeCon();
    	return count;  	
    }
    
    
    ////////////////////////////////////////////////AP分析部分//////////////////////////////////////
  //获取每个城市的热点总数*******************************
    public int returnApcount(String where){
    	int count;
    	List<String> list = new ArrayList<String>();
    	try{
    		String strSql = "select * from rm_device ".concat(where);
    		ResultSet rs = db.executeQuery(strSql);
    		if(rs!=null){
    			while(rs.next()){
    				list.add(rs.getString("device_id"));
    			}
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	count = list.size();
    	db.closeCon();
    	return count;  	
    }
  //获取已测的APMAC地址***********************************************
    public List<String> returnApmac(String where) {
    	  List<String> list = new ArrayList<String>();
          try {
              String strSql = "select distinct strapmac from  wlan_test_result ".concat(where);
              ResultSet rs = db.executeQuery(strSql);
              if (rs != null){
            	 while (rs.next()) {
                     list.add(rs.getString("strapmac"));
  				}
              }
          } catch (Exception e) {
              e.printStackTrace();
          }
          db.closeCon();
          return list;
      } 
  //获取某一城市已测的热点的数量
    public int returntestedApcount(String where,String city){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnApmac(where);  //获取已测的热点ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strmac_addr ='"+list.get(i)+"'"; 
        }
    	where2 +=") and strcity = '"+city+"'";
    	
    	try {
            String strSql = "select * from  rm_device " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("device_id"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
        db.closeCon();
    	return list2.size();
    }
    
    ////////////////////////////////////////////////////////任务分析部分//////////////////////////////
  //获取某一城市热点的ID
    public List<String> returnHpid(String city){
        List<String> list = new ArrayList<String>();  
    	
    	try {
            String strSql = "select * from  wlan_resc_hotpot where strcity =  '"+city+"'";
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list.add(rs.getString("strid"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        db.closeCon();
    	return list;
    }
    //获取每个城市下发任务数量
    public int returnTaskcount(String city){
    	String where2 = " where 1=1 ";
        List<String> list = returnHpid(city);  //获取已测的热点ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strhotpotname ='"+list.get(i)+"'"; 
        }
    	where2 +=")";
    	
    	try {
            String strSql = "select * from  wlan_task_info " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("strtaskid"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
        db.closeCon();
    	return list2.size();
    }
    //获取地市已测任务的热点ID
    public List<String> returntestedTaskid(String where){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnTskId(where);  //获取已测的任务ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strtaskid ='"+list.get(i)+"'"; 
        }
    	where2 +=")";
    	
    	try {
            String strSql = "select * from  wlan_task_info " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("strhotpotname"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
        db.closeCon();
    	return list2;
    }
    //获取地市已测任务的数量
    public int returntestedTaskcount(String where,String city){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returntestedTaskid(where);  //获取已测的热点ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strid ='"+list.get(i)+"'"; 
        }
    	where2 +=") and strcity = '"+city+"'";
    	
    	try {
            String strSql = "select * from  wlan_resc_hotpot " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("strid"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
        db.closeCon();
    	return list2.size();
    }
    
    ////////////////////////////////////人员分析部分////////////////////////////////////
    //获取每个地市的测试人员数量
    public int returnMancount(String where){
    	int count;
    	List<String> list = new ArrayList<String>();
    	try{
    		String strSql = "select * from wlan_sys_maintainer where strmainaddr = '"+where+"'";
    		ResultSet rs = db.executeQuery(strSql);
    		if(rs!=null){
    			while(rs.next()){
    				list.add(rs.getString("strmainid"));
    			}
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	count = list.size();
    	db.closeCon();
    	return count;  	
    }
    //获取已完成的测试的测试人姓名
    public List<String> returnManname(String where){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnTskId(where);  //获取已测的任务ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strtaskid ='"+list.get(i)+"'"; 
        }
    	where2 +=")";
    	
    	try {
            String strSql = "select * from  wlan_task_info " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("strtaskman"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        db.closeCon();
    	return list2;
    }
    //获取已完成的测试的测试人手机号
    public List<String> returnMantel(String where){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnTskId(where);  //获取已测的任务ID
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = new ArrayList<String>();
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strtaskid ='"+list.get(i)+"'"; 
        }
    	where2 +=")";
    	
    	try {
            String strSql = "select * from  wlan_task_info " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list2.add(rs.getString("strtaskphone"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //System.out.println("已*******测人&&&&&&&员手机号大小"+list2.size());
        db.closeCon();
    	return list2;
    }
    //由已完成的测试的测试人名和手机号确定其中属于某个地市的数量
    public int returntestedMancount(String where,String city){
    	String where2 = " where 1=1 ";
        where="";
        List<String> list = returnManname(where);  //测试人名
 	   	where2 += "and ( 1=2"; 
 	   	List<String> list2 = returnMantel(where);  //测试人手机
    	   
        for (int i = 0; i < list.size(); i++) {
			where2 += " or strmainname ='"+list.get(i)+"'"; 
        }
    	where2 +=") and ( 1=2";
    	for (int i=0; i < list2.size(); i++){
    		where2 += " or strmainmobile ='"+list2.get(i)+"'";
    	}
    	where2 += ") and strmainaddr = '"+city+"'";
    	
    	List<String> list3 = new ArrayList<String>();
    	try {
            String strSql = "select * from  wlan_sys_maintainer " + where2;
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null){
          	 while (rs.next()) {
                   list3.add(rs.getString("strmainid"));
				}
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
        db.closeCon();
    	return list3.size();
    }
}